package com.st.util;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class DBUtil {
	
	private static Connection conn1 = null;
	
	// 获取连接对象的
	public Connection getConn() throws ClassNotFoundException, SQLException {
		
		if (conn1 != null) {
			return conn1;
		}
		
		Class.forName("com.mysql.jdbc.Driver");

		String url = "jdbc:mysql://127.0.0.1:3306/javaee?useSSL=false";
		String user = "root";
		String password = "root";
		Connection conn = DriverManager.getConnection(url, user, password);
		
		conn1 = conn;
		return conn;
	}

	// 资源释放
	public void close(Connection conn, Statement stmt, ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
//		try {
//			if (conn != null)
//				conn.close();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
	}

	/**
	 * 对数据库进行insert, delete, update的操作
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(String sql, Object[] params) {

		Connection conn = null;
		// Statement stmt = null;
		PreparedStatement stmt = null;

		try {
			conn = getConn();

			// stmt = conn.createStatement();
			stmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					// 绑定参数
					stmt.setObject(i + 1, params[i]);
				}
			}

			int res = stmt.executeUpdate();

			return res;

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, stmt, null);
		}

		return -1;
	}
	
	/**
	 * 查询数据库, 返回一个结果对象
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 */
	public <T> T queryOne(String sql, Object[] params, Class<T> cls) {
		
		List<T> list = query(sql, params, cls);
		if (list == null) {
			return null;
		}
		
		if (list.size() > 0) {
			return list.get(0);
		} 
		
		return null;
	}

	/**
	 * 将Map对象, 转成 指定类型的对象
	 * @param map
	 * @param cls
	 * @return
	 */
	private <T> T mapToObject(Map<String, Object> map, Class<T> cls) {
		// {sid: 1, sname: Tom, ssex:男, sphone: 13312341234 }
		// 内省
		try {
			BeanInfo beanInfo = Introspector.getBeanInfo(cls);
			PropertyDescriptor[] props = beanInfo.getPropertyDescriptors();
			
			// 创建T类型的对象
			T obj = cls.newInstance();
			
			for (PropertyDescriptor prop : props) {
				
				// 属性名
				String propName = prop.getName();
				
				// 判断属性名, 在查询结果中有没有对应的值
				if (map.containsKey(propName)) {
					
					// 取值
					Object value = map.get(propName);
					// 找到属性的Set方法
					Method setter = prop.getWriteMethod();
					
					if (value != null && setter !=null) {
						setter.invoke(obj, value);			
					}
				}
			}
			
			return obj;
			
		} catch (IntrospectionException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	/**
	 * 查询数据库, 并将返回多条结果, 结果封装成指定的类的对象
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 */
	public <T> List<T> query(String sql, Object[] params, Class<T> cls) {

		// 列表中的元素从哪儿来?
		List<Map<String, Object>> list = query(sql, params);  // 执行SQL语句, 将查询结果返回过来.
		if (list == null) {
			return null;
		}
		
		// 返回值 
		List<T> resList = new ArrayList<>();		
		
		// 遍历所有的Map, 将Map转成T类型的对象
		for (int i=0; i<list.size(); i++) {
			// 取出了查询结果中的一条记录
			Map<String, Object> map = list.get(i);
			
			T t = mapToObject(map, cls); // 将Map转成指定类型的对象
			
			if (t!=null) {
				resList.add(t);
			}
		}
		
		return resList;
	}
		
	/**
	 * 查询数据库, 返回多条结果, 每一条结果封装成一个Map的对象
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> query(String sql, Object[] params) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = getConn();

			stmt = conn.prepareStatement(sql);
			// 绑定参数
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}

			rs = stmt.executeQuery();
			
			// 查询结果中有几个字段
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();	// 字段的数量

			// 把查查询到的结果封装成一个Clazz对象
			ArrayList<Map<String, Object>> list = new ArrayList<>();
			
			while (rs.next()) {
				// 将查询到的记录, 每一条都包装成一个Map对象
				Map<String, Object> map = new HashMap<String, Object>();
				
				for (int i=0; i<columnCount; i++) {
					String name = md.getColumnLabel(i+1);
					Object value = rs.getObject(i+1);
				
					map.put(name, value);
				}
				
				list.add(map);
			}

			return list;

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, stmt, rs);
		}

		return null;
	}

	/**
	 * 查询数据库, 返回一条结果, 结果被封装成Map对象
	 * @param sql
	 * @param params
	 * @return
	 */
	public Map queryOne(String sql, Object[] params) {
		List<Map<String, Object>> list = query(sql, params);

		if (list != null && list.size() > 0) {
			return list.get(0);
		}

		return null;
	}
}
